package com.amazon.service.impl;

import com.amazon.model.*;
import com.amazon.service.ShopService;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;
import java.util.concurrent.ThreadLocalRandom;

@Service
public class ShopServiceImpl implements ShopService {
    @Override
    public ShopListModel getShopList() {
        ShopListModel  shopListModel = new ShopListModel();
        List<ShopModel> shopModels = new ArrayList<>();
        InputStream inputStream = null;
        Workbook workbook = null;
        try {
            File file = new File("D:\\data\\amazon_data.xlsx");
            inputStream = new FileInputStream(file);
            //InputStream inputStream = LoadExcelService.getFileStream();
            workbook = new XSSFWorkbook(inputStream);
            Sheet sheet = workbook.getSheetAt(2);
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                ShopModel shopModel = new ShopModel();
                // 假设第一列是String类型，第二列是Double类型(getNumericCellValue)
                shopModel.setImageSrc(row.getCell(0).getStringCellValue());
                shopModel.setProductDetails(row.getCell(1).getStringCellValue());
                shopModel.setSellThrough(row.getCell(2).getStringCellValue());
                shopModel.setSalesSummary(row.getCell(3).getStringCellValue());
                shopModel.setLowInventoryLevelFee(row.getCell(4).getStringCellValue());
                shopModel.setInventoryOverview(row.getCell(5).getStringCellValue());
                shopModel.setInventoryHealthStatusAndRecommendation(row.getCell(6).getStringCellValue());
                shopModel.setEstimatedExcessUnits ((int) row.getCell(7).getNumericCellValue());
                shopModel.setInventoryAge(row.getCell(8).getStringCellValue());
                shopModel.setEstimatedAgedInventorySurcharge(row.getCell(9).getStringCellValue());
                shopModel.setEstimatedFeePerUnitSold(row.getCell(10).getStringCellValue());
                shopModel.setYourPrice(row.getCell(11).getNumericCellValue());
                shopModel.setDetailUrl(row.getCell(12).getStringCellValue());

                shopModel.setHistoricalDaysOfSupply(row.getCell(13).getStringCellValue());
                shopModel.setAvailable((int) row.getCell(14).getNumericCellValue());
                shopModel.setInboundQuantity(row.getCell(15).getStringCellValue());
                shopModel.setReserved(row.getCell(16).getStringCellValue());
                shopModel.setUnfulfillable(row.getCell(17).getStringCellValue());
                shopModels.add(shopModel);
            }
            Sheet sheet0 = workbook.getSheetAt(0);
            Row row = sheet0.getRow(1);
            String storeName = "";
            String webSite = "";
            int num = 0;
            if(row != null){
                Cell cell = row.getCell(0);
                storeName = cell.getStringCellValue();
                Cell cell2 = row.getCell(1);
                webSite = cell2.getStringCellValue();
                Cell cell3 = row.getCell(19);
                num = (int) cell3.getNumericCellValue();
            }
            shopListModel.setStore(storeName);
            shopListModel.setWebsite(webSite);
            shopListModel.setShopModelList(shopModels);
            shopListModel.setStockNum(num);
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            if(workbook != null){
                try {
                    workbook.close();
                } catch (Exception e){
                    e.printStackTrace();
                }

            }
            if(inputStream != null){
                try {
                    inputStream.close();
                } catch (Exception e){
                    e.printStackTrace();
                }

            }
        }
        return shopListModel;
    }

    @Override
    public CompanyModel getShopPersonInfo() {
        CompanyModel  companyModel = new CompanyModel();
        InputStream inputStream = null;
        Workbook workbook = null;
        try {
            File file = new File("D:\\data\\amazon_data.xlsx");
            inputStream = new FileInputStream(file);
            workbook = new XSSFWorkbook(inputStream);
            Sheet sheet = workbook.getSheetAt(0);
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                companyModel.setStore(row.getCell(0).getStringCellValue());
                //companyModel.setIsBrand((int) row.getCell(1).getNumericCellValue());
                String website = row.getCell(1).getStringCellValue();
                companyModel.setWebsite(website);
                //companyModel.setSelectedDateRange((int) row.getCell(3).getNumericCellValue());
                //companyModel.setSelectedDateRangeOneYearAgo((int) row.getCell(4).getNumericCellValue());
                companyModel.setLegalBusinessName(row.getCell(2).getStringCellValue());
                companyModel.setAddressLine1(row.getCell(3).getStringCellValue());
                companyModel.setAddressLine2(row.getCell(4).getStringCellValue());
                companyModel.setAddressLine3(row.getCell(5).getStringCellValue());
                companyModel.setZip((int) row.getCell(6).getNumericCellValue());
                companyModel.setPersonName(row.getCell(7).getStringCellValue());
                companyModel.setPersonAddress1(row.getCell(8).getStringCellValue());
                companyModel.setPersonAddress2(row.getCell(9).getStringCellValue());
                companyModel.setPersonAddress3(row.getCell(10).getStringCellValue());
                companyModel.setCity(row.getCell(11).getStringCellValue());
                companyModel.setCounty(row.getCell(12).getStringCellValue());
                companyModel.setPhoneNumber(row.getCell(13).getStringCellValue());
                companyModel.setNation(row.getCell(16).getStringCellValue());
            }
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            if(workbook != null){
                try {
                    workbook.close();
                } catch (Exception e){
                    e.printStackTrace();
                }

            }
            if(inputStream != null){
                try {
                    inputStream.close();
                } catch (Exception e){
                    e.printStackTrace();
                }

            }
        }
        return companyModel;
    }

    @Override
    public ShopBodyModel getShopBody() {
        ShopBodyModel shopBody = new ShopBodyModel();
        InputStream inputStream = null;
        Workbook workbook = null;
        try {
            File file = new File("D:\\data\\amazon_data.xlsx");
            inputStream = new FileInputStream(file);
            workbook = new XSSFWorkbook(inputStream);
            Random random = new Random();
            int min = 700;
            int max = 900;
            int randomNumber = random.nextInt(max - min + 1) + min;
            shopBody.setHealthRate(randomNumber);
            int unSellMin = 80;
            int unSellMax = 90;
            int unSellRandomNumber = random.nextInt(unSellMax - unSellMin + 1) + unSellMin;
            shopBody.setUnSellerNum(unSellRandomNumber);
            int sellMin = 2900;
            int sellMax = 3000;
            int sellRandomNumber = random.nextInt(sellMax - sellMin + 1) + sellMin;
            shopBody.setSellerNum(sellRandomNumber);
            int sellRate = (int) Math.floor((unSellRandomNumber * 100 / sellRandomNumber)) ;
            shopBody.setSellerFulfilled(sellRate);

            int unAmazonMin = 30;
            int unAmazonMax = 50;
            int unAmazonRandomNumber = random.nextInt(unAmazonMax - unAmazonMin + 1) + unAmazonMin;
            shopBody.setUnAmazonNum(unAmazonRandomNumber);
            int amazonMin = 5000;
            int amazonMax = 6000;
            int amazonRandomNumber = random.nextInt(amazonMax - amazonMin + 1) + amazonMin;
            shopBody.setAmazonNum(amazonRandomNumber);
            int amazonRate = (int) Math.floor((double) unAmazonRandomNumber * 100 / amazonRandomNumber);
            shopBody.setFulfilledAmazon(1);

            int trackingNumMin = 2550;
            int trackingNumMax = 2600;
            int trackingNum = random.nextInt(trackingNumMax - trackingNumMin + 1) + trackingNumMin;
            shopBody.setTrackingNum(trackingNum);
            int trackingAllNumMin = 2600;
            int trackingAllNumMax = 2650;
            int trackingAllNum = random.nextInt(trackingAllNumMax - trackingAllNumMin + 1) + trackingAllNumMin;
            shopBody.setTrackingAllNum(trackingAllNum);
            int trackingRate = (int) Math.floor((double) trackingNum * 100 / trackingAllNum);
            shopBody.setTrackingRate(trackingRate);
            Sheet sheet0 = workbook.getSheetAt(0);
            Row row = sheet0.getRow(1);
            String storeName = "";
            String webSite = "";
            if(row != null){
                Cell cell = row.getCell(0);
                storeName = cell.getStringCellValue();
                Cell cell2 = row.getCell(1);
                webSite = cell2.getStringCellValue();
            }
            shopBody.setStore(storeName);
            shopBody.setWebsite(webSite);
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            if(workbook != null){
                try {
                    workbook.close();
                } catch (Exception e){
                    e.printStackTrace();
                }

            }
            if(inputStream != null){
                try {
                    inputStream.close();
                } catch (Exception e){
                    e.printStackTrace();
                }

            }
        }

        return shopBody;
    }

    @Override
    public BusinessModel getShopAddr() {
        BusinessModel businessModel = new BusinessModel();
        InputStream inputStream = null;
        Workbook workbook = null;
        try {
            Thread.sleep(2000);
            File file = new File("D:\\data\\amazon_data.xlsx");
            inputStream = new FileInputStream(file);
            workbook = new XSSFWorkbook(inputStream);
            Sheet sheet = workbook.getSheetAt(0);
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                businessModel.setStore(row.getCell(0).getStringCellValue());
                String website = row.getCell(1).getStringCellValue();
                businessModel.setWebsite(website);
                businessModel.setAddress1(row.getCell(8).getStringCellValue());
                businessModel.setAddress2(row.getCell(15).getStringCellValue());
                businessModel.setAddress3(row.getCell(9).getStringCellValue());
                businessModel.setAddress4(row.getCell(14).getStringCellValue());
                businessModel.setAddress5(row.getCell(17).getStringCellValue());
                businessModel.setPhoneNumber(row.getCell(13).getStringCellValue());
            }

        } catch (Exception e){
            e.printStackTrace();
        } finally {
            if(workbook != null){
                try {
                    workbook.close();
                } catch (Exception e){
                    e.printStackTrace();
                }

            }
            if(inputStream != null){
                try {
                    inputStream.close();
                } catch (Exception e){
                    e.printStackTrace();
                }

            }
        }
        return businessModel;
    }

    public static void main(String[] args) {
        int a  = 2521 * 100 / 2681;
        System.out.println(a);
        int sellRate = (int) Math.floor((double) (23 / 25) * 100) ;
    }
}
